/**
 * 
 */
package com.sian.gencode.tablestructure;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.sian.base.utils.DBPropertiesUtil;





/**
 * 表的结构
 * @author desert
 * @version 1.0 2014/02/17
 */
public class TableUtils{
	
	/**
	 * 连接mysql数据库
	 * @param tableName 表的名称
	 * 
	 */
	public  List<TableStructs>  connectDataBase(String tableName,String databaseName)throws Exception{
		
		List<TableStructs>  ts_list = new ArrayList<TableStructs>();
		  
		DBPropertiesUtil  dbutile = new DBPropertiesUtil();
		
		Connection con = null; //表示数据库的连接对象  
        Statement stmt = null;  //表示数据库的更新操作  
        Class.forName(dbutile.jdbcdrive); //1、使用CLASS 类加载驱动程序  
        con = DriverManager.getConnection(dbutile.jdbcurl,dbutile.jdbcuser,dbutile.jdbcpassword); //2、连接数据库  
        stmt = con.createStatement(); //3、Statement 接口需要通过Connection 接口进行实例化操
		
//		  String sql = "SELECT tco.COLUMN_NAME,tco.IS_NULLABLE,tco.COLUMN_TYPE,tco.COLUMN_KEY,tco.COLUMN_COMMENT,tco.CHARACTER_MAXIMUM_LENGTH " +
//		  		"FROM information_schema.COLUMNS tco  WHERE tco.TABLE_NAME='"+tableName+"' AND tco.TABLE_SCHEMA='"+databaseName+"'";
		  String sql = "SELECT * " +
				  "FROM information_schema.COLUMNS tco  WHERE tco.TABLE_NAME='"+tableName+"'";

		  String sql1 = "SELECT *, (case when tco.column_name=(select b.column_name from information_schema.table_constraints a inner join information_schema.constraint_column_usage b on a.constraint_name = b.constraint_name where a.constraint_type = 'PRIMARY KEY' and a.table_name = '"+tableName+"') then 'PRI' else '' END )as COLUMN_KEY FROM information_schema.COLUMNS tco  WHERE tco.TABLE_NAME='"+tableName+"' ";

		  ResultSet rs = stmt.executeQuery(sql1);
		  while(rs.next()){
  				   TableStructs  ts_obj = new TableStructs();
  				  
  				   ts_obj.setColumnName(rs.getString("COLUMN_NAME"));
  				   
  				   ts_obj.setColumnisNull(rs.getString("IS_NULLABLE")); //是否允许为空
  				   
  				   ts_obj.setColumnType(rs.getString("data_TYPE"));
  				   
  				   ts_obj.setColumniskey(rs.getString("COLUMN_KEY"));
  				   
  				  // ts_obj.setColumnComent(rs.getString("COLUMN_COMMENT"));
//  				   ts_obj.setColumnType(rs.getString("COLUMN_TYPE"));
//  				   
//  				   ts_obj.setColumniskey(rs.getString("COLUMN_KEY"));
//  				   
//  				   ts_obj.setColumnComent(rs.getString("COLUMN_COMMENT"));
  				   
  				   ts_obj.setTextlength(rs.getString("CHARACTER_MAXIMUM_LENGTH"));//文本的最大长度
  				   
  				   ts_list.add(ts_obj);
  			  
  		  }
  		  
        rs.close();  
        con.close(); // 4、关闭数据库  
        
        return ts_list;
	}
	
	/**
	 * 生成pojo
	 * @param tableName
	 * @param databaseName
	 * @return
	 */
	public String construtsPojo(String tableName,String databaseName){
		
		StringBuffer sb = new StringBuffer();
		
		try {
			List<TableStructs>  ts_list = this.connectDataBase(tableName, databaseName);
			
			if(ts_list!=null&&ts_list.size()>0){
				
				
				//import class
				sb.append("");
				
				boolean iskeyExist = false;
				
				String prototype = "";
				//产生属性
				for(TableStructs obj : ts_list){
			          System.out.println(obj.getColumnName()+"  "+obj.getColumnType()+"  "+obj.getColumniskey());
			          
			          sb.append("      @GenField(labelname=\""+obj.getColumnComent()+"\",column=\""+obj.getColumnName()+"\"");
			          if(obj.getColumniskey()!=null&&obj.getColumniskey().toLowerCase().equals("pri")&&!iskeyExist){
			        	  sb.append(",id=true");
			          }
			          
			          //字符的长度
			          String length = obj.getTextlength();
			          if(length!=null&&!length.trim().equals("")){
			        	  sb.append(",length="+length);
			          }
			          
			          
			          //生成页面验证时使用
			          String isnull = obj.getColumnisNull();
			          
			          if(isnull!=null&&isnull.toLowerCase().trim().equals("no")){
			        	  sb.append(",isnull=false");
			          }else{
			        	  sb.append(",isnull=true");
			          }
			          
			          String columnType = obj.getColumnType().toLowerCase();
			          
			          sb.append(") \r\n");
			          //对数据库中的属性分类
			          //包含int的数据类型为 int
			          if(columnType.indexOf("int")!=-1){
			        	  prototype = "Integer";
			          }
			          
			          //判断字符型
			          if(columnType.indexOf("blob")!=-1||columnType.indexOf("text")!=-1||columnType.indexOf("varchar")!=-1){
			        	  prototype = "String";
			          }
			          
			          //判断日期型
			          if(columnType.indexOf("date")!=-1||columnType.indexOf("year")!=-1
			        		           ||columnType.indexOf("time")!=-1){
			        	  prototype = "Date";
			          }
			          
			          //判断否点型
			          if(columnType.indexOf("real")!=-1||columnType.indexOf("float")!=-1||columnType.indexOf("numeric")!=-1){
			        	  
			        	  prototype = "Float";
			          }
			          
			          //双精度
			          if(columnType.indexOf("double")!=-1){
			        	  prototype = "Double";
			          }
			          
			          //bit
			          if(columnType.indexOf("bit")!=-1){
			        	  prototype = "Short";
			          }
			        
			          //二进制  暂时不 提供生成
			          
			          sb.append("      private "+prototype+" "+obj.getColumnName()+"; \r\n");
			        	
			    }
				
			   //构建setter getter方法
				
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return sb.toString();
	}
	
	
	public static void main(String[] arg)throws Exception{
		TableUtils  tu = new TableUtils();
		
		List<TableStructs> list = tu.connectDataBase("oa_dept", "jdoa");
		System.out.println(list);
       
	 	String sb = tu.construtsPojo("oa_dept", "jdoa");
	 	
	 	System.out.println(sb.toString());
	}

	
}
